package com.github.data_manage.service

import com.github.data_manage.common.MONTH_FORMATTER
import com.github.data_manage.config.SystemException
import com.github.data_manage.dao.*
import com.github.data_manage.domain.dto.Quadruple
import com.github.data_manage.domain.po.Fund
import com.github.data_manage.domain.po.FundDepthResearch
import com.github.data_manage.domain.po.FundInternalScore
import com.github.data_manage.domain.po.FundInternalScoreHistory
import com.github.data_manage.domain.vo.FundDepthResearchQueryVo
import com.github.data_manage.util.DataUtils
import com.github.data_manage.util.DataUtils.avg
import com.github.data_manage.util.DataUtils.standardDeviation
import com.github.data_manage.util.DataUtils.nav
import com.github.data_manage.util.DateTimeUtils
import com.github.data_manage.util.DateTimeUtils.Companion.atEndOfMonth
import jakarta.servlet.http.HttpServletResponse
import jakarta.transaction.Transactional
import kotlinx.coroutines.*
import org.apache.poi.xssf.usermodel.XSSFWorkbook
import org.springframework.http.HttpHeaders
import org.springframework.http.MediaType
import org.springframework.jdbc.core.JdbcTemplate
import org.springframework.stereotype.Service
import java.math.BigDecimal
import java.math.RoundingMode
import java.net.URLEncoder
import java.nio.charset.StandardCharsets
import java.time.LocalDate
import java.time.Period
import java.util.*
import kotlin.math.abs
import kotlin.math.pow

@Service
open class FundDeepResearchService(
    private val fundDepthResearchDao: FundDepthResearchDao,
    private val fundDao: FundDao,
    private val fundBusinessDataDao: FundBusinessDataDao,
    private val fundReturnDao: FundReturnDataDao,
    private val fundNavDataDao: FundNavDataDao,
    private val contrastBusinessDataDao: ContrastBusinessDataDao,
    private val fundInternalScoreDao: FundInternalScoreDao,
    private val fundInternalScoreHistoryDao: FundInternalScoreHistoryDao,
    private val jdbcTemplate: JdbcTemplate
) {

    companion object {
        val THREE: BigDecimal = BigDecimal.valueOf(3)

        val FOUR: BigDecimal = BigDecimal.valueOf(4)
    }


    @Transactional(rollbackOn = [Exception::class])
    open fun save(fundDepthResearch: FundDepthResearch) {
        val fund = fundDao.findById(fundDepthResearch.fundId!!).orElseThrow { SystemException("基金未找到") }
        fundDepthResearchDao.findByFundIdEquals(fundDepthResearch.fundId!!)?.let {
            fundDepthResearch.fundId = it.fundId
            fundDepthResearch.id = it.id
        }
        if (fundDepthResearch.id == null) {
            fundDepthResearchDao.save(fundDepthResearch)
        }
//        fund.aum = fundDepthResearch.aum
//        fundDao.save(fund)

        handleDepthResearchInfo(fund, fundDepthResearch)
    }

    open fun findByFundId(fundId: Int): FundDepthResearch? = fundDepthResearchDao.findByFundIdEquals(fundId)

    fun handleDepthResearchInfo(fund: Fund, fundDepthResearch: FundDepthResearch) = runBlocking {
        fundDepthResearch.aum = fund.aum
        val mddRateTask = fundMddRate(fund)
        val fundHrInfoTask = fundHrInfo(fund)
        val fundAvInfoTask = fundAvInfo(fund)
        val fundSrRateTask = fundSrRate(fund)
        val fundManagerTask = fundManager(fundDepthResearch)
        val fundArRankRateTask = fundArRankRate(fund)
        val fundBusinessTask = fundBusiness(fund, fundDepthResearch)
        val fundBataRateTask = fundBetaRate(fund, fundDepthResearch.beta)

        awaitAll(
            mddRateTask,
            fundHrInfoTask,
            fundAvInfoTask,
            fundSrRateTask,
            fundManagerTask,
            fundBusinessTask,
            fundBataRateTask,
            fundArRankRateTask
        )

        fundDepthResearch.mddRate = mddRateTask.await()
        val (fundHitRadio, fundHrRank, fundHrRate) = fundHrInfoTask.await()
        fundDepthResearch.hitRatio = fundHitRadio
        fundDepthResearch.hrRank = fundHrRank
        fundDepthResearch.hrRate = fundHrRate

        val (fundAv, fundAvRank, fundAvRate) = fundAvInfoTask.await()
        fundDepthResearch.annVol = fundAv
        fundDepthResearch.avRank = fundAvRank
        fundDepthResearch.avRate = fundAvRate

        val fundSrRate = fundSrRateTask.await()
        fundDepthResearch.srRate = fundSrRate

        val fundManagerRate = fundManagerTask.await()
        fundDepthResearch.keyManRate = fundManagerRate

        var establishmentDate = fund.createDate?.let { DateTimeUtils.tryParse(it) };
        if (establishmentDate == null) {
            establishmentDate = LocalDate.now()
        }

        fundDepthResearch.mons = calculateMons(establishmentDate);

        val (businessRate, midBackRate, aumRate, monsRate) = fundBusinessTask.await()
        fundDepthResearch.businessRate = businessRate
        fundDepthResearch.midBackRate = midBackRate
        fundDepthResearch.aumRate = aumRate
        fundDepthResearch.monsRate = monsRate
        fundDepthResearch.monsRank = monsRate

        val (fundBeta, fundBetaRate) = fundBataRateTask.await()
//        fundDepthResearch.beta = fundBeta
        fundDepthResearch.betaRate = fundBetaRate

        fundDepthResearch.overallReturnRate =
            fundAvRate.add(fundSrRate).add(fundHrRate).divide(THREE, 0, RoundingMode.HALF_UP)
        fundDepthResearch.overAllRiskRate = fundAvRate.add(fundDepthResearch.mddRate)
            .add(monsRate).add(fundBetaRate).divide(FOUR, 0, RoundingMode.HALF_UP)
        fundDepthResearch.riskRate = (fundDepthResearch.overAllRiskRate ?: BigDecimal.ZERO).multiply(BigDecimal("0.5"))
            .add(fundDepthResearch.careAboutInvestors ?: BigDecimal.ZERO).multiply(BigDecimal("0.1"))
            .add(fundDepthResearch.rational ?: BigDecimal.ZERO).multiply(BigDecimal("0.1"))
            .add(fundDepthResearch.riskSense ?: BigDecimal.ZERO).multiply(BigDecimal("0.1"))
            .add(fundDepthResearch.investorBaseRate ?: BigDecimal.ZERO).multiply(BigDecimal("0.1"))
            .add(
                when (fund.aum ?: BigDecimal.ZERO) {
                    in BigDecimal.ZERO..BigDecimal.valueOf(100) -> 1
                    in BigDecimal.valueOf(100)..BigDecimal.valueOf(500) -> 2
                    in BigDecimal.valueOf(500)..BigDecimal.valueOf(1000) -> 3
                    else -> 0
                }.toBigDecimal().multiply(BigDecimal("0.1"))
            ).setScale(1, RoundingMode.HALF_UP)

        fundDepthResearch.screenOverallRate = (fundDepthResearch.meetingRate ?: BigDecimal.ZERO)
            .add(fundDepthResearch.investmentRate ?: BigDecimal.ZERO)
            .add(fundDepthResearch.rational ?: BigDecimal.ZERO)
            .add(fundDepthResearch.riskRate ?: BigDecimal.ZERO)
            .add(fundDepthResearch.keyManRate ?: BigDecimal.ZERO)
            .add(fundDepthResearch.businessRate ?: BigDecimal.ZERO).let {
                if (it.compareTo(BigDecimal.ZERO) == 0) null else it.divide(
                    BigDecimal.valueOf(6), 1, RoundingMode.HALF_UP
                )
            }



        val (investmentOverview, investmentRate) = fundInvestmentOverView(fund, fundDepthResearch)
        fundDepthResearch.investmentOverview = investmentOverview
        fundDepthResearch.investmentRate = investmentRate

        val (annReturn, arRankRate) = fundArRankRateTask.await()
        fundDepthResearch.annReturn = annReturn
        fundDepthResearch.arRankRate = arRankRate

        fundDepthResearchDao.findByFundIdEquals(fundDepthResearch.fundId!!)?.let {
            fundDepthResearch.fundId = it.fundId
            fundDepthResearch.id = it.id
        }

        fundDepthResearchDao.save(fundDepthResearch)

        val fundInternalScore = fundInternalScoreDao.findByFundId(fund.fundId!!)
            .orElse(FundInternalScore.empty(fund.fundId!!, fund.fundName!!))

        fundInternalScoreHistoryDao.save(FundInternalScoreHistory(fund.fundId!!, fundInternalScore))

        fundInternalScore.earnings = (fundDepthResearch.investmentOverview ?: BigDecimal.ZERO).setScale(
            1,
            RoundingMode.HALF_UP
        ).toDouble()
        fundInternalScore.internalResearch = (fundDepthResearch.meetingRate ?: BigDecimal.ZERO).setScale(
            1,
            RoundingMode.HALF_UP
        ).toDouble()
        fundInternalScore.commercialization = (fundDepthResearch.businessRate ?: BigDecimal.ZERO).setScale(
            1,
            RoundingMode.HALF_UP
        ).toDouble()
        fundInternalScore.managerMaturity = (fundDepthResearch.keyManRate ?: BigDecimal.ZERO).setScale(
            1,
            RoundingMode.HALF_UP
        ).toDouble()
        fundInternalScore.clause = (fundDepthResearch.liquidityTerms ?: BigDecimal.ZERO).setScale(
            1,
            RoundingMode.HALF_UP
        ).toDouble()
        fundInternalScore.portfolioManagement =
            (fundDepthResearch.portfolioConstructioRate ?: BigDecimal.ZERO).setScale(
                1,
                RoundingMode.HALF_UP
            ).toDouble()
        fundInternalScore.risk = (fundDepthResearch.overAllRiskRate ?: BigDecimal.ZERO).setScale(
            1,
            RoundingMode.HALF_UP
        ).toDouble()

        fundInternalScoreDao.save(fundInternalScore)
    }


    private suspend fun fundMddRate(fund: Fund): Deferred<BigDecimal> = CoroutineScope(Dispatchers.Default).async {
        val allFundReturns = fundReturnDao.findMaxReturnData()
        val fundReturnData = allFundReturns.firstOrNull { it.fundId!! == fund.fundId!! }
        val fundMaxReturnValue = fundReturnData?.value ?: BigDecimal.ZERO
        val mddRate =
            if (fundMaxReturnValue!!.compareTo(BigDecimal.ZERO) == 0) {
                THREE
            } else {
                val allFundMaxReturns = allFundReturns.map { it.value }
                when (allFundMaxReturns.indexOf(fundMaxReturnValue) * 100 / allFundMaxReturns.size) {
                    in (0..30) -> THREE
                    in (30..60) -> BigDecimal.valueOf(2)
                    in (60..90) -> BigDecimal.valueOf(1)
                    else -> BigDecimal.ZERO
                }
            }
        return@async mddRate
    }

    private fun fundInvestmentOverView(fund: Fund, fundDepthResearch: FundDepthResearch): Pair<BigDecimal, BigDecimal> {
        val investmentOverview =
            if (fundDepthResearch.investmentPhilosophy == null || fundDepthResearch.investmentPhilosophy!!.compareTo(
                    BigDecimal.ZERO
                ) == 0
            ) {
                BigDecimal.ONE
            } else {
                val investmentIdeaValue =
                    (fundDepthResearch.investmentPhilosophy!! + (fundDepthResearch.investmentProcess
                        ?: BigDecimal.ZERO) + (fundDepthResearch.researchTeamRate
                        ?: BigDecimal.ZERO)).divide(THREE, 0, RoundingMode.HALF_UP)

                val investmentPerformanceValue =
                    ((fundDepthResearch.overallReturnRate ?: BigDecimal.ZERO) + (fundDepthResearch.overAllRiskRate
                        ?: BigDecimal.ZERO) + (fundDepthResearch.portfolioConstructioRate ?: BigDecimal.ZERO))
                        .divide(THREE, 0, RoundingMode.HALF_UP)
                investmentIdeaValue.multiply(BigDecimal("0.3"))
                    .add(investmentPerformanceValue.multiply(BigDecimal("0.7")))
                    .setScale(0, RoundingMode.HALF_UP)
            }

        val investmentRate = when ((fund.aum ?: BigDecimal.ZERO)) {
            in BigDecimal("2000")..BigDecimal(Int.MAX_VALUE) -> investmentOverview.subtract(BigDecimal("0.5"))
            in BigDecimal("1000")..BigDecimal("2000") -> investmentOverview.subtract(BigDecimal("0.2"))
            else -> investmentOverview
        }.setScale(1, RoundingMode.HALF_UP)
        return Pair(investmentOverview, investmentRate)
    }

    private suspend fun fundHrInfo(fund: Fund): Deferred<Triple<BigDecimal, BigDecimal, BigDecimal>> =
        CoroutineScope(Dispatchers.Default).async {
            val fundHitRadioMap = HashMap<Int, BigDecimal>()
            for ((fundId, fundBusinessDataList) in fundBusinessDataDao.findAll().groupBy { it.fundId!! }) {
                val fundHitRadio = fundBusinessDataList.count { it.value > BigDecimal.ZERO }
                    .toBigDecimal().divide(fundBusinessDataList.size.toBigDecimal(), 2, RoundingMode.HALF_UP)
                    .multiply(BigDecimal.valueOf(100))
                fundHitRadioMap[fundId] = fundHitRadio
            }
            val allFundHitRadios = fundHitRadioMap.values.sortedDescending()

            val fundHrRankMap = HashMap<Int, BigDecimal>()
            for ((fundId, hitRatio) in fundHitRadioMap) {
                val fundHrRank = (allFundHitRadios.indexOf(hitRatio) * 100).toBigDecimal()
                    .divide(allFundHitRadios.size.toBigDecimal(), 2, RoundingMode.HALF_UP)
                fundHrRankMap[fundId] = fundHrRank
            }

            val fundHrRateMap = HashMap<Int, BigDecimal>()
            for ((fundId, hrRank) in fundHrRankMap) {
                val fundHrRate = when (hrRank) {
                    in BigDecimal.ZERO..BigDecimal.valueOf(30) -> THREE
                    in BigDecimal.valueOf(30)..BigDecimal.valueOf(60) -> BigDecimal.valueOf(2)
                    in BigDecimal.valueOf(60)..BigDecimal.valueOf(90) -> BigDecimal.valueOf(1)
                    else -> BigDecimal.ZERO
                }
                fundHrRateMap[fundId] = fundHrRate
            }
            return@async Triple(
                fundHitRadioMap[fund.fundId!!] ?: BigDecimal.ZERO,
                fundHrRankMap[fund.fundId!!] ?: BigDecimal.ZERO,
                fundHrRateMap[fund.fundId!!] ?: BigDecimal.ONE
            )
        }

    private suspend fun fundAvInfo(fund: Fund): Deferred<Triple<BigDecimal, BigDecimal, BigDecimal>> =
        CoroutineScope(Dispatchers.Default).async {
            val fundAvMap = HashMap<Int, BigDecimal>()
            val fundBusinessMap = fundBusinessDataDao.findAll().groupBy { it.fundId!! }
            for ((fundId, fundBusinessDataList) in fundBusinessMap) {
                val fundLastBusinessData = fundBusinessDataList.maxBy { it.time!!.year }!!
                val fundAv = fundBusinessDataList
                    .filter { it.time!!.year == fundLastBusinessData.time!!.year }
                    .map { it.value.divide(BigDecimal.valueOf(100), 4, RoundingMode.HALF_UP) }.standardDeviation()
                fundAvMap[fundId] = fundAv
            }
            val allFundAvs = fundAvMap.values.sorted()
            val fundAvRankMap = HashMap<Int, BigDecimal>()
            for ((fundId, fundAv) in fundAvMap) {
                val fundAvRank = (allFundAvs.indexOf(fundAv) * 100).toBigDecimal()
                    .divide(allFundAvs.size.toBigDecimal(), 2, RoundingMode.HALF_UP)
                fundAvRankMap[fundId] = fundAvRank
            }
            val fundAvRateMap = HashMap<Int, BigDecimal>()
            for ((fundId, hrRank) in fundAvRankMap) {
                val fundAvRate = when (hrRank) {
                    in BigDecimal.ZERO..BigDecimal.valueOf(30) -> THREE
                    in BigDecimal.valueOf(30)..BigDecimal.valueOf(60) -> BigDecimal.valueOf(2)
                    in BigDecimal.valueOf(60)..BigDecimal.valueOf(90) -> BigDecimal.valueOf(1)
                    else -> BigDecimal.ZERO
                }
                fundAvRateMap[fundId] = fundAvRate
            }
            return@async Triple(
                fundAvMap[fund.fundId!!] ?: BigDecimal.ZERO,
                fundAvRankMap[fund.fundId!!] ?: BigDecimal.ZERO,
                fundAvRateMap[fund.fundId!!] ?: BigDecimal.ONE
            )
        }

    private suspend fun fundSrRate(fund: Fund): Deferred<BigDecimal> = CoroutineScope(Dispatchers.Default).async {
        val allBusinessDataMap = fundBusinessDataDao.findAll().groupBy { it.fundId!! }
        val allFundNavDataMap = fundNavDataDao.findAll().groupBy { it.fundId!! }

        if (allBusinessDataMap.isEmpty() || allFundNavDataMap.isEmpty()) {
            return@async BigDecimal.ZERO
        }
        val fundSharpRadioMap = HashMap<Int, BigDecimal>()
        fundDao.findAll().forEach { dbFund ->
            val allBusinessData = (allBusinessDataMap[dbFund.fundId!!] ?: emptyList()).sortedBy { it.time }
            val allFundNavData = allFundNavDataMap[dbFund.fundId!!] ?: emptyList()
            if (allBusinessData.isEmpty() || allFundNavData.isEmpty()) {
                fundSharpRadioMap[dbFund.fundId!!] = BigDecimal.ZERO
                return@forEach
            }
            val lastBusinessData = allBusinessData.last()

//            val mothStandardDeviation = DataUtils.calcMonthStandardDeviation(allBusinessData.map { it.value })
            val yearStandardDeviation = allBusinessData.filter { it.time!!.year == lastBusinessData.time!!.year }
                .map { it.value.divide(BigDecimal.valueOf(100), 4, RoundingMode.HALF_UP) }.standardDeviation()
//            val yearStandardDeviation = mothStandardDeviation.multiply(BigDecimal.valueOf(sqrt(12.0)))
//                .setScale(2, RoundingMode.HALF_UP);
            val currentFundNavValue = allFundNavData.first().value.setScale(4, RoundingMode.HALF_UP)

//            val yearNav = BigDecimal.valueOf(
//                currentFundNavValue.divide(BigDecimal(100)).toDouble().pow(12.0 / allFundNavData.size) - 1
//            ).multiply(BigDecimal(100)).setScale(4, RoundingMode.HALF_UP)
            val yearNav = allBusinessData.filter { it.time!!.year == lastBusinessData.time!!.year }
                .map { it.value.divide(BigDecimal.valueOf(100), 4, RoundingMode.HALF_UP) }.nav()

            val sharpRatio = if (yearStandardDeviation.compareTo(BigDecimal.ZERO) == 0) {
                BigDecimal.ZERO
            } else {
                (yearNav.subtract(BigDecimal.valueOf(4))).divide(
                    yearStandardDeviation,
                    4,
                    RoundingMode.HALF_UP
                )
            }
            fundSharpRadioMap[dbFund.fundId!!] = sharpRatio
        }

        val allFundSharpRadios = fundSharpRadioMap.values.sortedDescending()
        val fundSharpRadio = fundSharpRadioMap[fund.fundId!!] ?: BigDecimal.ZERO
        val srRate = when (allFundSharpRadios.indexOf(fundSharpRadio) * 100 / allFundSharpRadios.size) {
            in 0..30 -> 3
            in 30..60 -> 2
            in 60..90 -> 1
            else -> 0
        }.toBigDecimal()
        return@async srRate
    }

    private suspend fun fundArRankRate(fund: Fund): Deferred<Pair<BigDecimal, BigDecimal>> =
        CoroutineScope(Dispatchers.Default).async {
            val fundAnnReturnMap = HashMap<Int, BigDecimal>()
            fundBusinessDataDao.findAll().groupBy { it.fundId!! }
                .forEach { fundId, fundBusinessDatas ->
                    val fundBusinessValues =
                        fundBusinessDatas.map { it.value.divide(BigDecimal(100), RoundingMode.HALF_UP) }
                    val product = (fundBusinessValues.reduce { a, b ->
                        (a + BigDecimal.ONE) * (BigDecimal.ONE + b)
                    } - BigDecimal.ONE).setScale(2, RoundingMode.HALF_UP)
                    val count = fundBusinessValues.size
                    val annReturnDouble = (product + BigDecimal.ONE).toDouble().pow(12.0 / count - 1)
                    val annReturn = annReturnDouble.toBigDecimal().setScale(2, RoundingMode.HALF_UP)
                    fundAnnReturnMap[fundId] = annReturn
                }
            val allFundReturns = fundAnnReturnMap.values.sortedDescending()
            val arRankRate =
                when (allFundReturns.indexOf(fundAnnReturnMap[fund.fundId!!]) * 100 / allFundReturns.size) {
                    in 0..30 -> 3
                    in 30..60 -> 2
                    in 60..90 -> 1
                    else -> 0
                }.toBigDecimal()
//            return@async Pair(fundAnnReturnMap[fund.fundId!!] ?: BigDecimal.ZERO, arRankRate)

            return@async Pair(fund.yearNav ?: BigDecimal.ZERO, arRankRate)
        }

    private suspend fun fundManager(fundDepthResearch: FundDepthResearch): Deferred<BigDecimal> =
        CoroutineScope(Dispatchers.Default).async {

            val condition =
                (fundDepthResearch.careAboutInvestors == null || fundDepthResearch.careAboutInvestors!!.compareTo(
                    BigDecimal.ZERO
                ) == 0) &&
                        (fundDepthResearch.independentThinking == null || fundDepthResearch.independentThinking!!.compareTo(
                            BigDecimal.ZERO
                        ) == 0) &&
                        (fundDepthResearch.openMinded == null || fundDepthResearch.openMinded!!.compareTo(BigDecimal.ZERO) == 0) &&
                        (fundDepthResearch.rational == null || fundDepthResearch.rational!!.compareTo(BigDecimal.ZERO) == 0) &&
                        (fundDepthResearch.riskSense == null || fundDepthResearch.riskSense!!.compareTo(BigDecimal.ZERO) == 0) &&
                        (fundDepthResearch.tradeSense == null || fundDepthResearch.tradeSense!!.compareTo(BigDecimal.ZERO) == 0) &&
                        (fundDepthResearch.learn == null || fundDepthResearch.learn!!.compareTo(BigDecimal.ZERO) == 0) &&
                        (fundDepthResearch.talent == null || fundDepthResearch.talent!!.compareTo(BigDecimal.ZERO) == 0) &&
                        (fundDepthResearch.vision == null || fundDepthResearch.vision!!.compareTo(BigDecimal.ZERO) == 0)
            if (condition) {
                return@async BigDecimal("1.5")
            }
            return@async listOf(
                (fundDepthResearch.workHard ?: BigDecimal.ZERO),
                (fundDepthResearch.careAboutInvestors ?: BigDecimal.ZERO),
                (fundDepthResearch.independentThinking ?: BigDecimal.ZERO),
                (fundDepthResearch.openMinded ?: BigDecimal.ZERO),
                (fundDepthResearch.rational ?: BigDecimal.ZERO),
                (fundDepthResearch.riskSense ?: BigDecimal.ZERO),
                (fundDepthResearch.tradeSense ?: BigDecimal.ZERO),
                (fundDepthResearch.learn ?: BigDecimal.ZERO),
                (fundDepthResearch.talent ?: BigDecimal.ZERO),
                (fundDepthResearch.vision ?: BigDecimal.ZERO),
                (fundDepthResearch.intelligence ?: BigDecimal.ZERO),
            ).avg().setScale(1, RoundingMode.HALF_UP)
//            return@async ((fundDepthResearch.workHard ?: BigDecimal.ZERO).multiply(BigDecimal("0.1")) +
//                    (
//                            (fundDepthResearch.careAboutInvestors ?: BigDecimal.ZERO) +
//                                    (fundDepthResearch.independentThinking ?: BigDecimal.ZERO) +
//                                    (fundDepthResearch.openMinded ?: BigDecimal.ZERO) +
//                                    (fundDepthResearch.rational ?: BigDecimal.ZERO) +
//                                    (fundDepthResearch.riskSense ?: BigDecimal.ZERO) +
//                                    (fundDepthResearch.tradeSense ?: BigDecimal.ZERO) +
//                                    (fundDepthResearch.learn ?: BigDecimal.ZERO) +
//                                    (fundDepthResearch.talent ?: BigDecimal.ZERO) +
//                                    (fundDepthResearch.vision ?: BigDecimal.ZERO)
//                            )
//                        .divide(BigDecimal.valueOf(9), 2, RoundingMode.HALF_UP) * BigDecimal("0.9"))
//                .setScale(1, RoundingMode.HALF_UP)
        }

    private suspend fun fundBetaRate(
        fund: Fund,
        fundBetaValue: BigDecimal?
    ): Deferred<Pair<BigDecimal, BigDecimal>> = CoroutineScope(Dispatchers.Default).async {
        // 找到每一个基金最新的12个月的数据
        val fundLast12MonthValuesMap = HashMap<Int, MutableList<BigDecimal>>()
        jdbcTemplate.query(
            """
            SELECT d.fund_id, d.value
            FROM t_fund_bussiness_data d
                     JOIN (SELECT fund_id,
                                  MAX(time) AS max_date
                           FROM t_fund_bussiness_data
                           GROUP BY fund_id) m ON
                d.fund_id = m.fund_id AND
                d.time BETWEEN DATE_SUB(m.max_date, INTERVAL 12 MONTH) AND m.max_date
            ORDER BY d.fund_id, d.time desc;
        """.trimIndent()
        ) { rs, rowNum ->
            val fundId = rs.getInt(1)
            val fundValues = fundLast12MonthValuesMap[fundId] ?: mutableListOf<BigDecimal>()
            fundValues.add(rs.getBigDecimal(2))
            fundLast12MonthValuesMap[fundId] = fundValues
        }
        val contrastLast12MonthValues = contrastBusinessDataDao.findLast12Values(1).map { it.value }
        if (contrastLast12MonthValues.size < 12) {
            return@async Pair(BigDecimal.ZERO, THREE)
        }
        val contrastMean = contrastLast12MonthValues.avg()

        val fundBetaMap = HashMap<Int, BigDecimal>()
        for ((fundId, currentFundLast12MonthValues) in fundLast12MonthValuesMap) {
            if (contrastLast12MonthValues.size < 12) {
                continue
            }
            val fundMean = currentFundLast12MonthValues.avg()
            // 2. 计算协方差 Cov(rf, rm)
            val covariance = currentFundLast12MonthValues.zip(contrastLast12MonthValues).sumOf { (rfi, rmi) ->
                (rfi - fundMean) * (rmi - contrastMean)
            }.divide(BigDecimal.valueOf(11), 2, RoundingMode.HALF_UP)
            // 3. 计算基准方差 Var(rm)
            val varianceRm = contrastLast12MonthValues.sumOf { rmi ->
                (rmi - contrastMean).pow(2)
            }.divide(BigDecimal.valueOf(11), 2, RoundingMode.HALF_UP)
            val beta = (covariance / varianceRm).setScale(2, RoundingMode.HALF_UP).abs()
            fundBetaMap[fundId] = beta
        }
        if (!fundBetaMap.containsKey(fund.fundId!!)) {
            return@async Pair(BigDecimal.ZERO, BigDecimal.ZERO)
        }
        val betaList = fundBetaMap.values.sorted()
        val fundBeta = fundBetaValue ?: fundBetaMap[fund.fundId!!]!!
        val betaRate = when (DataUtils.indexOfNumberBySize(fundBeta, betaList) * 100 / betaList.size) {
            in 0..30 -> 3
            in 30..60 -> 2
            in 60..90 -> 1
            else -> 0
        }.toBigDecimal()
        return@async Pair(fundBeta.multiply(BigDecimal.valueOf(100)), betaRate)
    }

    fun calculateMons(establishmentDate: LocalDate?): Int {
        val currentDate = LocalDate.now()

        // 计算完整的年数和月数
        val period = Period.between(establishmentDate, currentDate)

        // 转换为总月数（忽略天数）
        return period.years * 12 + period.months
    }

    private suspend fun fundBusiness(
        fund: Fund,
        fundDepthResearch: FundDepthResearch
    ): Deferred<Quadruple<BigDecimal, BigDecimal, BigDecimal, BigDecimal>> =
        CoroutineScope(Dispatchers.Default).async {

            val mons = fundDepthResearch.mons ?: 0
            val monsRate = when (mons) {
                in 0..6 -> 0
                in 6..12 -> 1
                in 12..36 -> 2
                else -> 3
            }.toBigDecimal()
            val aumRate = when (fundDepthResearch.aum ?: BigDecimal.ZERO) {
                in BigDecimal.valueOf(100)..BigDecimal.valueOf(500) -> 3
                in BigDecimal.valueOf(500)..BigDecimal.valueOf(Long.MAX_VALUE) -> 2
                else -> 0
            }.toBigDecimal()

            val sumRate = (fundDepthResearch.salesRate ?: BigDecimal.ZERO)
                .add(
                    fundDepthResearch.cooRate ?: BigDecimal.ZERO
                )
                .add(
                    fundDepthResearch.midOfficeRate ?: BigDecimal.ZERO
                )
                .add(
                    fundDepthResearch.investorBaseRate ?: BigDecimal.ZERO
                )
            val midBackRate = if (sumRate.compareTo(BigDecimal.ZERO) == 0) {
                BigDecimal.ONE
            } else {
                sumRate.divide(BigDecimal.valueOf(4), 1, RoundingMode.HALF_UP)
            }
            val businessRate = if (monsRate.compareTo(BigDecimal.ZERO) == 0) {
                BigDecimal("1.5")
            } else {
                monsRate.add(aumRate)
                    .divide(BigDecimal.valueOf(2), 1, RoundingMode.HALF_UP)
                    .multiply(BigDecimal("0.3"))
                    .add(midBackRate.multiply(BigDecimal("0.7")))
            }
            return@async Quadruple(businessRate, midBackRate, aumRate, monsRate)
        }


    fun exportFundDepthResearch(
        fundDepthResearchQueryVo: FundDepthResearchQueryVo,
        servletResponse: HttpServletResponse
    ) {
        val workBook = XSSFWorkbook()
        val sheet = workBook.createSheet()

        sheet.createRow(0).let { conditionRow ->
            var conditionCount = 1
            listOf(
                fundDepthResearchQueryVo.screenOverallRateCondition,
                fundDepthResearchQueryVo.aumRateCondition,
                fundDepthResearchQueryVo.meetingRateCondition,
                fundDepthResearchQueryVo.investmentRateCondition,
                fundDepthResearchQueryVo.keyManRateCondition,
            ).filter { Objects.nonNull(it) }
                .forEach { condition ->
                    val conditionCountCell = conditionRow.createCell((conditionCount - 1) * 2)
                    conditionCountCell.setCellValue("筛选条件$conditionCount")
                    val conditionCell = conditionRow.createCell((conditionCount - 1) * 2 + 1)
                    conditionCell.setCellValue(condition.toString())
                    conditionCount++
                }
        }

        val fundTimeRow = sheet.createRow(1)
        fundTimeRow.createCell(0).setCellValue("基金")

        for (i in 0..12) {
            fundTimeRow.createCell(i + 1)
                .setCellValue(LocalDate.now().minusMonths((12 - i).toLong()).atEndOfMonth().format(MONTH_FORMATTER))
        }

        var fundDataRowNum = 2
        val lastYearTime = LocalDate.now().minusMonths(12).atEndOfMonth()
        listFundDepthResearch(fundDepthResearchQueryVo).forEach { fundId ->
            val fundDataRow = sheet.createRow(fundDataRowNum)
            val fund = fundDao.findById(fundId).orElseThrow()
            fundDataRow.createCell(0).setCellValue(fund.fundName)
            fundBusinessDataDao.findLastOneYearData(fundId, lastYearTime).sortedBy { it.time }
                .forEach { businessData ->
                    val cellIndex = 12 - abs(Period.between(LocalDate.now(), businessData.time).months)
                    fundDataRow.createCell(cellIndex).setCellValue(businessData.value.toDouble())
                }
            fundDataRowNum++;
        }

        servletResponse.contentType = MediaType.APPLICATION_OCTET_STREAM_VALUE
        servletResponse.characterEncoding = StandardCharsets.UTF_8.name()
        servletResponse.setHeader(
            HttpHeaders.CONTENT_DISPOSITION, "filename=" + URLEncoder.encode(
                "基金信息.xlsx", StandardCharsets.UTF_8
            )
        )
        servletResponse.outputStream.use { fos ->
            workBook.write(fos)
        }
    }

    fun listFundDepthResearch(fundDepthResearchQueryVo: FundDepthResearchQueryVo): List<Int> {
        val aumRateCondition = fundDepthResearchQueryVo.aumRateConditionFilter()
        val meetingRateCondition = fundDepthResearchQueryVo.meetingRateConditionFilter()
        val investmentRateCondition = fundDepthResearchQueryVo.investmentRateConditionFilter()
        val screenOverallRateCondition = fundDepthResearchQueryVo.screenOverallRateConditionFilter()
        val keyManRateCondition = fundDepthResearchQueryVo.keyManRateConditionFilter()
        return fundDepthResearchDao.findAllFundIds(
            fundDepthResearchQueryVo.invested,
            aumRateCondition,
            meetingRateCondition,
            investmentRateCondition,
            screenOverallRateCondition,
            keyManRateCondition,
        )
    }

}
